package com.kelai.service.impl;

import com.google.common.base.Joiner;
import com.google.common.collect.*;
import com.kelai.common.IRepository;
import com.kelai.common.constants.AccountTypeEnum;
import com.kelai.common.constants.DeviceStatusEnum;
import com.kelai.common.constants.WebAppConstants;
import com.kelai.domain.*;
import com.kelai.repository.ICustomerRepository;
import com.kelai.repository.IDeviceDayDataRepository;
import com.kelai.repository.IDeviceRepository;
import com.kelai.repository.ISaleDataRepository;
import com.kelai.service.*;
import com.kelai.utils.BreezeeUtils;
import com.kelai.utils.DynamicSpecifications;
import com.kelai.utils.SystemTool;
import org.apache.commons.collections4.CollectionUtils;
import org.olap4j.*;
import org.olap4j.metadata.Member;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.DriverManager;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.time.temporal.TemporalField;
import java.time.temporal.WeekFields;
import java.util.*;
import java.util.stream.Collectors;

import static com.kelai.utils.BreezeeUtils.DATE_FORMAT_SHORT;
import static java.time.format.DateTimeFormatter.ofPattern;

/**
 * 统计服务
 * Created by Silence on 2016/11/27.
 */
@Service("staticService")
public class DefaultStaticService implements IStaticService {
    private final static Logger logger = LoggerFactory.getLogger(DefaultStaticService.class);
    private final static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Resource
    private IAccountService accountService;
    @Resource
    private ICustomerService customerService;

    @Resource
    private IShopDayService shopDayService;
    @Resource
    private IShopWeekService shopWeekService;
    @Resource
    private IShopMonthService shopMonthService;

    private final ICustomerRepository customerRepository;

    private final IDeviceDayDataRepository deviceDayDataRepository;

    private final IDeviceRepository deviceRepository;

    private final ISaleDataRepository saleDataRepository;

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public DefaultStaticService(ICustomerRepository customerRepository, IDeviceDayDataRepository deviceDayDataRepository, IDeviceRepository deviceRepository, ISaleDataRepository saleDataRepository, JdbcTemplate jdbcTemplate) {
        this.customerRepository = customerRepository;
        this.deviceDayDataRepository = deviceDayDataRepository;
        this.deviceRepository = deviceRepository;
        this.saleDataRepository = saleDataRepository;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public IRepository<EmptyEntity> getRepository() {
        return null;
    }

    @Override
    public EmptyEntity queryChartsData(Map<String, Object> map) {
        long s = System.currentTimeMillis();
        String shopIdsStr = map.get("shopIdsStr").toString();
        String dateType = map.get("dateType").toString(); //1昨天 2上周 3上月
        Integer ss = 1;
        if (map.get("offsetDay") != null)
            ss = Integer.parseInt(map.get("offsetDay").toString());

        Map<String, Object> resultMap = new HashMap<>();
        //统计客流数据
        List<String> chartNames = Lists.newArrayList();
        List<String> chartDatas = Lists.newArrayList();
        if (!StringUtils.isEmpty(shopIdsStr)) {
            List<String> shopIdList = Arrays.asList(shopIdsStr.split("-"));
            if(shopIdList.size()>20){
                shopIdList = shopIdList.subList(0,20);
            }
            Map<String, Object> condition = Maps.newConcurrentMap();

            if (Objects.equals(dateType, "2")) {
                TemporalField fieldISO = WeekFields.of(Locale.CHINESE).dayOfWeek();
                String startDate = LocalDate.now().minusWeeks(1).with(fieldISO, 1).toString();
                String endDate = LocalDate.now().minusWeeks(1).with(fieldISO, 7).toString();
                condition.put("_startDate", startDate);
                condition.put("_endDate", endDate);
                condition.put("_shopIds", shopIdList);
                long s1 = System.currentTimeMillis();
                List<ShopDataWeekEntity> resultList = shopWeekService.listAll(condition);
                long e1 = System.currentTimeMillis();
                logger.info("queryChartsData方法中bi执行时间："+(e1-s1)/1000+"s");
                if (CollectionUtils.isNotEmpty(resultList)) {
                    resultList.sort((h1, h2) -> h1.getDxin().compareTo(h2.getDxin()));
                    //totalDxin = shopDataDayList.stream().mapToInt( shopDataDay -> shopDataDay.getDxin()).sum();
                    for (ShopDataWeekEntity item : resultList) {
//                        chartNames.add(_shopName(item.getShopName()));
                        chartNames.add(item.getShopName());
                        chartDatas.add(String.valueOf(item.getDxin()));
                    }
                }
            } else if (Objects.equals(dateType, "3")) {
                String startDate = LocalDate.now().minusMonths(1).with(TemporalAdjusters.firstDayOfMonth()).toString();
                String endDate = LocalDate.now().minusMonths(1).with(TemporalAdjusters.lastDayOfMonth()).toString();
                condition.put("_startDate", startDate);
                condition.put("_endDate", endDate);
                condition.put("_shopIds", shopIdList);
                long s2 = System.currentTimeMillis();
                List<ShopDataMonthEntity> resultList = shopMonthService.listAll(condition);
                long e2 = System.currentTimeMillis();
                logger.info("queryChartsData方法中bi执行时间："+(e2-s2)/1000+"s");
                if (CollectionUtils.isNotEmpty(resultList)) {
                    resultList.sort((h1, h2) -> h1.getDxin().compareTo(h2.getDxin()));
                    //totalDxin = shopDataDayList.stream().mapToInt( shopDataDay -> shopDataDay.getDxin()).sum();
                    for (ShopDataMonthEntity item : resultList) {
//                        chartNames.add(_shopName(item.getShopName()));
                        chartNames.add(item.getShopName());
                        chartDatas.add(String.valueOf(item.getDxin()));
                    }
                }
            } else if (Objects.equals(dateType, "4")) { //TODO 移动端首页，上周客流统计
                TemporalField fieldISO = WeekFields.of(Locale.CHINESE).dayOfWeek();
//                String startDate = LocalDate.now().minusWeeks(1).with(fieldISO, 1).plusDays(1).toString();
//                String endDate = LocalDate.now().minusWeeks(1).with(fieldISO, 7).plusDays(1).toString();

                String startDate = StringUtils.isEmpty(map.get("_startDate")) ? LocalDate.now().minusWeeks(1).with(fieldISO, 1).plusDays(0).toString() : map.get("_startDate").toString();
                String endDate = StringUtils.isEmpty(map.get("_endDate")) ? LocalDate.now().minusWeeks(1).with(fieldISO, 7).plusDays(0).toString() : map.get("_endDate").toString();

                condition.put("_startDate", startDate);
                condition.put("_endDate", endDate);
                condition.put("_shopIds", shopIdList);
                long s3 = System.currentTimeMillis();
                List<ShopDataDayEntity> resultList = shopDayService.listAll(condition);
                long e3 = System.currentTimeMillis();
                logger.info("queryChartsData方法中bi执行时间："+(e3-s3)/1000+"s");
                if (CollectionUtils.isNotEmpty(resultList)) {
                    resultList.sort((h1, h2) -> h1.getDxin().compareTo(h2.getDxin()));
                    Multimap<Integer, ShopDataDayEntity> multimap = ArrayListMultimap.create();
                    for (ShopDataDayEntity item : resultList) {
                        multimap.put(SystemTool.getDayOfWeek(item.getHappenTime()), item);
                    }
                    Map<Integer, Integer> cntMap = Maps.newConcurrentMap();
                    Map<Integer, Collection<ShopDataDayEntity>> groupMap = multimap.asMap();
                    for (Map.Entry<Integer, Collection<ShopDataDayEntity>> dayCnt : groupMap.entrySet()) {
                        Integer tempIn = 0;
                        for (ShopDataDayEntity item : dayCnt.getValue()) {
                            tempIn += item.getDxin();
                        }
                        cntMap.put(dayCnt.getKey(), tempIn);
                    }

                    chartNames = Lists.newArrayList("周一", "周二", "周三", "周四", "周五", "周六", "周日");
                    List<Integer> tempList = Lists.newArrayList(1, 2, 3, 4, 5, 6, 7);
                    for (Integer item : tempList) {
                        chartDatas.add(String.valueOf(cntMap.get(item)));
                    }
                }
            } else {
                String startDate = StringUtils.isEmpty(map.get("_startDate")) ? LocalDate.now().minusDays(ss).toString() : map.get("_startDate").toString();
                String endDate = StringUtils.isEmpty(map.get("_endDate")) ? LocalDate.now().minusDays(ss).toString() : map.get("_endDate").toString();
                condition.put("_startDate", startDate);
                condition.put("_endDate", endDate);
                condition.put("_shopIds", shopIdList);
                long s4 = System.currentTimeMillis();
                List<ShopDataDayEntity> resultList = shopDayService.listAll(condition);
                long e4 = System.currentTimeMillis();
                logger.info("queryChartsData方法中bi执行时间："+(e4-s4)/1000+"s");
                if (CollectionUtils.isNotEmpty(resultList)) {
                    resultList.sort((h1, h2) -> h1.getDxin().compareTo(h2.getDxin()));
                    //totalDxin = shopDataDayList.stream().mapToInt( shopDataDay -> shopDataDay.getDxin()).sum();
                    for (ShopDataDayEntity item : resultList) {
//                        chartNames.add(_shopName(item.getShopName()));
                        chartNames.add(item.getShopName());
                        chartDatas.add(String.valueOf(item.getDxin()));
                    }
                }
            }

            resultMap.put("shopTotal", shopIdList.size());
        }

        resultMap.put("chartNames", chartNames);
        resultMap.put("chartDatas", chartDatas);

        long e = System.currentTimeMillis();
        logger.info("queryChartsData方法执行时间："+(e-s)/1000+"s");
        return new EmptyEntity(resultMap);
    }

    private String _shopName(String name) {
        String ty = name.substring(0, 2);
        String tt = name.substring(2);
        if(tt.length()>11)
            tt = tt.substring(0,11);
        String[] names = tt.split("");
        StringBuilder sb = new StringBuilder();
        sb.append(ty).append("\n");
        for (String s : names) {
            s = s.trim();
            if (s.length() > 0) {
                sb.append(s).append("\n");
            }
        }
        return sb.toString();
    }

    @Override
    public EmptyEntity getCountStaticInfo(String accountId) {
        long s = System.currentTimeMillis();
        Map<String, Object> resultMap = new HashMap<>();
        //根据帐号找店铺
        List<Object[]> oriShopList = Lists.newArrayList();

        AccountEntity accountEntity = accountService.findById(accountId);
        if (Objects.equals(accountEntity.getType(), AccountTypeEnum.CUSTOMER.getValue())) {
            oriShopList = customerService.findShopsByCustomer(accountId);
        } else if (Objects.equals(accountEntity.getType(), AccountTypeEnum.VENDOR.getValue())) {
            oriShopList = customerService.findShopsByVendor(accountId);
        } else if (Objects.equals(accountEntity.getType(), AccountTypeEnum.SHOP.getValue())){
            oriShopList = customerService.findShopsByShop(accountId);
        } else {
            oriShopList = customerService.findShopsByAdmin();
        }
        List<Object[]> shopList = Lists.newArrayList();
        List<String> shopIdList = Lists.newArrayList();
        Date now = new Date();

        shopList = oriShopList.stream().filter(shop -> {
            Boolean flag = true;
            //开业时间
            if(shop[3] != null){
                if(now.compareTo((Date)shop[3]) < 0){
                    flag = false;
                }
            }
            //停业时间
            if(shop[4] != null){
                if(now.compareTo((Date)shop[4]) > 0){
                    flag = false;
                }
            }
            if(shop[5] == null || !Objects.equals(1,shop[5])){
                flag = false;
            }
            return flag;
        }).collect(Collectors.toList());
        shopIdList = shopList.stream().map(shop -> shop[0].toString()).collect(Collectors.toList());
        String shopIdsStr = "";

        Integer totalDxin = 0;
        if (CollectionUtils.isNotEmpty(shopIdList)) {
            Map<String, Object> totalCondition = Maps.newConcurrentMap();
            totalCondition.put("_startDate", LocalDate.now().toString());
            totalCondition.put("_endDate", LocalDate.now().toString());
            totalCondition.put("_shopIds", shopIdList);

            long s1 = System.currentTimeMillis();
            List<ShopDataDayEntity> resultList = shopDayService.listAll(totalCondition);
            long e1 = System.currentTimeMillis();
            logger.info("getCountStaticInfo方法中bi执行时间："+(e1-s1)/1000+"s");
            if (CollectionUtils.isNotEmpty(resultList)) {
                totalDxin  = resultList.stream().mapToInt( shopDataDay -> shopDataDay.getDxin()).sum();
            }

            shopIdsStr = Joiner.on("-").join(shopIdList);
//            String tempshopIdsStr = Joiner.on("','").join(shopIdList);
//            System.out.println(tempshopIdsStr);
        }
        resultMap.put("totalDxin", totalDxin);

        //根据店铺找设备
        List<DeviceEntity> deviceList = Lists.newArrayList();
        if (CollectionUtils.isNotEmpty(shopIdList)) {
            deviceList = deviceRepository.findDevicesByShops(shopIdList);
        }
        Integer[] ds = new Integer[]{0, 0};
        deviceList.forEach(device -> {
            if (device.getStatus() != DeviceStatusEnum.ENABLE.getValue()) {
                ds[1] += 1;
            }
            ds[0] += 1;
        });

        //获取所有店面
        List<String> shopNames = new ArrayList<String>();
        for (Object[] objs:shopList) {
            if(objs.length>=3){
                shopNames.add(objs[2].toString());
            }
        }

        resultMap.put("shopNames",shopNames);
        resultMap.put("shopIdList",shopIdList);

        resultMap.put("shopSize", shopList.size());
        resultMap.put("deviceSize", ds[0]);
        resultMap.put("exDeviceSize", ds[1]);
        resultMap.put("shopIdsStr", shopIdsStr);

        long e = System.currentTimeMillis();
        float excTime=(float)(e-s)/1000;
        logger.info("getCountStaticInfo方法执行时间："+excTime+"s");
        return new EmptyEntity(resultMap);
    }

    @Override
    public EmptyEntity getAllDevice(String accountId) {

        //根据帐号找店铺
        List<Object[]> oriShopList = Lists.newArrayList();

        AccountEntity accountEntity = accountService.findById(accountId);
        if (Objects.equals(accountEntity.getType(), AccountTypeEnum.CUSTOMER.getValue())) {
            oriShopList = customerService.findShopsByCustomer(accountId);

        } else if (Objects.equals(accountEntity.getType(), AccountTypeEnum.VENDOR.getValue())) {
            oriShopList = customerService.findShopsByVendor(accountId);

        } else {
            oriShopList = customerService.findShopsByAdmin();
        }


        List<Object[]> shopList = Lists.newArrayList();
        List<String> shopIdList = Lists.newArrayList();
        Date now = new Date();

        shopList = oriShopList.stream().filter(shop -> {
            Boolean flag = true;
            //开业时间
            if(shop[3] != null){
                if(now.compareTo((Date)shop[3]) < 0){
                    flag = false;
                }
            }
            //停业时间
            if(shop[4] != null){
                if(now.compareTo((Date)shop[4]) > 0){
                    flag = false;
                }
            }
            return flag;
        }).collect(Collectors.toList());
        shopIdList = shopList.stream().map(shop -> shop[0].toString()).collect(Collectors.toList());

        //根据店铺找设备
        List<DeviceEntity> deviceList = Lists.newArrayList();
        if (CollectionUtils.isNotEmpty(shopIdList)) {
            deviceList = deviceRepository.findDevicesByShops(shopIdList);
        }

        /*
        异常设备
        List<DeviceEntity> exceptionDevices = new ArrayList<>();
        Iterator<DeviceEntity> itor = deviceList.iterator();
        while(itor.hasNext()){
            DeviceEntity deviceEntity = itor.next();
            if(deviceEntity.getStatus() != DeviceStatusEnum.ENABLE.getValue()){
                exceptionDevices.add(deviceEntity);
            }
        }*/

        EmptyEntity emptyEntity = new EmptyEntity();

        emptyEntity.getProperties().put("exceptionDevices",deviceList);


        return emptyEntity;

    }

    @Override
    public EmptyEntity getDataStaticInfo(String shopId) {
        Map<String, Object> m = new HashMap<>();
        m.put("customer_id_obj_ae", shopId);
        List<DeviceEntity> l = deviceRepository.findAll(DynamicSpecifications.createSpecification(m));
        List<String> sns = new ArrayList<>();
        l.forEach(a -> {
            sns.add(a.getCode());
        });
        m.clear();
        LocalDate now = LocalDate.now();
        String day = now.plusDays(-1).toString();
        m.put("happenTime", Long.parseLong(day.replaceAll("-", "")));
        m.put("name_in", sns);
        List<DeviceDayDataEntity> d = deviceDayDataRepository.findAll(DynamicSpecifications.createSpecification(m));
        m.clear();
        m.put("customer_id_obj_ae", shopId);
        try {
            m.put("happenDate", sdf.parse(day + " 12:00:00"));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<SaleDataEntity> sd = saleDataRepository.findAll(DynamicSpecifications.createSpecification(m));
        m.clear();
        if (d != null && d.size() > 0) {
            m.put("dxin", d.get(0).getDxin());
            m.put("dxout", d.get(0).getDxout());
        }
        if (sd != null && sd.size() > 0) {
            m.put("saleData", sd.get(0));
        }
        return new EmptyEntity(m);
    }

    @Override
    public EmptyEntity getDeviceWeekData(String shopId) {
        Long start = 0L;
        Long end = 0L;
        LocalDate now = LocalDate.now();
        now = now.plusDays(0 - now.getDayOfWeek().getValue());
        end = Long.parseLong(now.toString().replaceAll("-", ""));
        start = Long.parseLong(now.plusDays(-7).toString().replaceAll("-", ""));
        Map<String, Object> m = new HashMap<>();
        m.put("customer_id_obj_ae", shopId);
        List<DeviceEntity> l = deviceRepository.findAll(DynamicSpecifications.createSpecification(m));
        StringBuilder sb = new StringBuilder();
        l.forEach(a -> {
            sb.append(",").append("'").append(a.getCode()).append("'");
        });
        if (sb.length() < 1)
            return new EmptyEntity();
        m.clear();
        List<Map<String, Object>> ret = jdbcTemplate.queryForList("SELECT * FROM (SELECT t.happen_time,sum(t.dxin) as dxin, sum(t.dxout) as dxout FROM t_device_data_day t " +
                        "WHERE t.happen_time>=? and t.happen_time<? and t.name in (?) " +
                        "GROUP BY t.happen_time ) g ORDER BY g.happen_time ASC ",
                start, end, sb.substring(1));
        List<Map<String, Object>> tret = jdbcTemplate.queryForList("SELECT * FROM (SELECT t.happen_time,sum(t.dxin) as dxin, sum(t.dxout) as dxout FROM t_device_data_day t " +
                        "WHERE t.happen_time>=? and t.name in (?) " +
                        "GROUP BY t.happen_time ) g ORDER BY g.happen_time ASC ",
                end, sb.substring(1));

        m.put("lastWeek", ret);
        m.put("thisWeek", tret);

        return new EmptyEntity(m);
    }

    @Override
    public EmptyEntity sumForRealTime(Map<String, Object> map) {
        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" ");
        strBuilder.append(" select sum(dxin) totalin,sum(dxout) totalout from t_device_data where 1=1 ");
        if (BreezeeUtils.isNotEmpty(map.get("_customerId"))) {
            strBuilder.append(" and customer_id = '" + map.get("_customerId") + "' ");
        }
        if (BreezeeUtils.isNotEmpty(map.get("customer_id_obj_ae"))) {
            strBuilder.append(" and cus_id = '" + map.get("customer_id_obj_ae") + "' ");
        }
        if (BreezeeUtils.isNotEmpty(map.get("device_id_obj_ae"))) {
            strBuilder.append(" and device_id = '" + map.get("device_id_obj_ae") + "' ");
        }
        if (BreezeeUtils.isNotEmpty(map.get("happenTime_gt"))) {
            strBuilder.append(" and happen_time > " + map.get("happenTime_gt"));
        }
        if (BreezeeUtils.isNotEmpty(map.get("happenTime_le"))) {
            strBuilder.append(" and happen_time <  " + map.get("happenTime_le"));
        }

        List<Map<String, Object>> resultList = jdbcTemplate.queryForList(strBuilder.toString());

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForDeviceDay(Map<String, Object> map) {
        /*StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" ");
        strBuilder.append(" select sum(dxin) totalin,sum(dxout) totalout ");
        strBuilder.append(" from T_DEVICE_DATA_DAY_BI where 1=1 ");
        if ( BreezeeUtils.isNotEmpty(map.get("_customerId"))) {
            strBuilder.append(" and customer_id = '"+map.get("_customerId")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("customer_id_obj_ae"))) {
            strBuilder.append(" and shop_id = '"+map.get("customer_id_obj_ae")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("device_id_obj_ae"))) {
            strBuilder.append(" and device_id = '"+map.get("device_id_obj_ae")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("_year"))) {
            strBuilder.append(" and year = '"+map.get("_year")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("happenTime_gt"))) {
            strBuilder.append(" and happen_time >= '" + DATE_FORMAT_SHORT.format(map.get("happenTime_gt"))+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("happenTime_le"))) {
            strBuilder.append(" and happen_time <=  '"+ DATE_FORMAT_SHORT.format(map.get("happenTime_le"))+"' ");
        }

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(strBuilder.toString());*/
        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append(" {   [设备].[设备ID].[设备ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[日].[日].ALLMEMBERS } on 1 ");
            strPageBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[日].&[" + map.get("_startDate") + "T00:00:00]:[时间].[年月日小时].[日].&[" + map.get("_endDate") + "T00:00:00] }, {");

            if (map.get("_deviceIds") != null && CollectionUtils.isNotEmpty((List<String>) map.get("_deviceIds"))) {
                ((List<String>) map.get("_deviceIds")).stream().forEach(deviceId -> {
                    strPageBuffer.append("  [设备].[设备ID].[设备ID].&[" + deviceId + "] , ");
                });
                strPageBuffer.append("  [设备].[设备ID].[设备ID].&[-1] ");
            } else {
                strPageBuffer.append(" [设备].[设备ID].[设备ID].&[" + map.get("device_id_obj_ae") + "]  ");
            }

            strPageBuffer.append(" } ) ON COLUMNS ");
            strPageBuffer.append(" FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            String deviceId = "";
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 5) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[4]));
                    dxout = dxout.add(new BigDecimal(ss[5]));
                }
                resultMap.put(ss[0], dxin+"-"+dxout);

            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForShopDay(Map<String, Object> map) {

        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append(" { [组织].[组织ID].[组织ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[日].[日].ALLMEMBERS } on 1 ");
            strPageBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[日].&[" + map.get("_startDate") + "T00:00:00]:[时间].[年月日小时].[日].&[" + map.get("_endDate") + "T00:00:00] },{ ");
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(map.get("_customerId") + "") &&
                    org.apache.commons.lang3.StringUtils.isEmpty(map.get("customer_id_obj_ae") + "")) {
                CustomerEntity parentCustomer = customerRepository.findOne(map.get("_customerId") + "");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                Object shopType = map.get("shopType");
                shops.stream().forEach(shop -> {
                    if (shopType != null && !Objects.equals("", shopType)) {
                        if (Objects.equals(shop.getShopType(), shopType)) {
                            strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    } else {
                        strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                    }
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            } else {
                strPageBuffer.append("  [组织].[组织层次].&[" + map.get("customer_id_obj_ae") + "]  ");
            }
            strPageBuffer.append(" } ) ON COLUMNS FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 5) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[4]));
                    dxout = dxout.add(new BigDecimal(ss[5]));
                }

            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForDeviceHour(Map<String, Object> map) {

        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            String startDate = String.valueOf(map.get("_startDate"));
            String endDate = String.valueOf(map.get("_endDate"));

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append("  {  [设备].[设备ID].[设备ID].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[日].[日].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[小时].[小时].ALLMEMBERS } on 1 ");
            strPageBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[小时].&[" + startDate + "T00:00:00]:[时间].[年月日小时].[小时].&[" + endDate + "T23:00:00]}, {");

            if (map.get("_deviceIds") != null && CollectionUtils.isNotEmpty((List<String>) map.get("_deviceIds"))) {
                ((List<String>) map.get("_deviceIds")).stream().forEach(deviceId -> {
                    strPageBuffer.append("  [设备].[设备ID].[设备ID].&[" + deviceId + "] , ");
                });
                strPageBuffer.append("  [设备].[设备ID].[设备ID].&[-1] ");
            } else {
                strPageBuffer.append(" [设备].[设备ID].[设备ID].&[" + map.get("device_id_obj_ae") + "]  ");
            }

            strPageBuffer.append(" }) ON COLUMNS ");
            strPageBuffer.append(" FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 6) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[5]));
                    dxout = dxout.add(new BigDecimal(ss[6]));
                }
            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForShopHour(Map<String, Object> map) {

        List<Map<String, String>> resultList = Lists.newArrayList();
        try {
            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            String startDate = String.valueOf(map.get("_startDate"));
            String endDate = String.valueOf(map.get("_endDate"));

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append("  {  ");
            strPageBuffer.append(" [组织].[组织ID].[组织ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[日].[日].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[小时].[小时].ALLMEMBERS} on 1 ");
            strPageBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[小时].&[" + startDate + "T00:00:00]:[时间].[年月日小时].[小时].&[" + endDate + "T23:00:00]},{ ");

            if (org.apache.commons.lang3.StringUtils.isNotEmpty(map.get("_customerId") + "") &&
                    org.apache.commons.lang3.StringUtils.isEmpty(map.get("customer_id_obj_ae") + "")) {
                CustomerEntity parentCustomer = customerRepository.findOne(map.get("_customerId") + "");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                Object shopType = map.get("shopType");
                shops.stream().forEach(shop -> {
                    if (shopType != null && !Objects.equals("", shopType)) {
                        if (Objects.equals(shop.getShopType(), shopType)) {
                            strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    } else {
                        strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                    }
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            } else {
                strPageBuffer.append("  [组织].[组织层次].&[" + map.get("customer_id_obj_ae") + "]  ");
            }

            strPageBuffer.append(" } ) ON COLUMNS FROM [客流]) ");


            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 6) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[5]));
                    dxout = dxout.add(new BigDecimal(ss[6]));
                }


            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;

    }

    @Override
    public EmptyEntity sumForDeviceWeek(Map<String, Object> map) {
        /*StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" ");
        strBuilder.append(" select sum(dxin) totalin,sum(dxout) totalout ");
        strBuilder.append(" from T_DEVICE_DATA_WEEK where 1=1 ");
        if ( BreezeeUtils.isNotEmpty(map.get("_customerId"))) {
            strBuilder.append(" and customer_id = '"+map.get("_customerId")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("customer_id_obj_ae"))) {
            strBuilder.append(" and shop_id = '"+map.get("customer_id_obj_ae")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("device_id_obj_ae"))) {
            strBuilder.append(" and device_id = '"+map.get("device_id_obj_ae")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("_year"))) {
            strBuilder.append(" and year = '"+map.get("_year")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("happenTime_gt"))) {
            strBuilder.append(" and happen_time > '" + DATE_FORMAT_SHORT.format(map.get("happenTime_gt"))+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("happenTime_le"))) {
            strBuilder.append(" and happen_time <  '"+ DATE_FORMAT_SHORT.format(map.get("happenTime_le"))+"' ");
        }

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(strBuilder.toString());*/

        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append("  { [设备].[设备ID].[设备ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[周].[周].ALLMEMBERS } on 1 ");
            strPageBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[日].&[" + map.get("_startDate") + "T00:00:00]:[时间].[年月日小时].[日].&[" + map.get("_endDate") + "T00:00:00]}, {");

            if (map.get("_deviceIds") != null && CollectionUtils.isNotEmpty((List<String>) map.get("_deviceIds"))) {
                ((List<String>) map.get("_deviceIds")).stream().forEach(deviceId -> {
                    strPageBuffer.append("  [设备].[设备ID].[设备ID].&[" + deviceId + "] , ");
                });
                strPageBuffer.append("  [设备].[设备ID].[设备ID].&[-1] ");
            } else {
                strPageBuffer.append(" [设备].[设备ID].[设备ID].&[" + map.get("device_id_obj_ae") + "]  ");
            }

            strPageBuffer.append(" } ) ON COLUMNS ");
            strPageBuffer.append(" FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 4) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[3]));
                    dxout = dxout.add(new BigDecimal(ss[4]));
                }
            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForShopWeek(Map<String, Object> map) {


        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append("  { subset ");
            strPageBuffer.append(" (( ");
            strPageBuffer.append(" [组织].[组织ID].[组织ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[周].[周].ALLMEMBERS )  ");
            strPageBuffer.append(" ,0,10)} on 1 ");
            strPageBuffer.append(" FROM ( SELECT ( { [时间].[年月日小时].[日].&[" + map.get("_startDate") + "T00:00:00]:[时间].[年月日小时].[日].&[" + map.get("_endDate") + "T00:00:00]}, {");
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(map.get("_customerId") + "") &&
                    org.apache.commons.lang3.StringUtils.isEmpty(map.get("customer_id_obj_ae") + "")) {
                CustomerEntity parentCustomer = customerRepository.findOne(map.get("_customerId") + "");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                Object shopType = map.get("shopType");
                shops.stream().forEach(shop -> {
                    if (shopType != null) {
                        if (Objects.equals(shop.getShopType(), shopType)) {
                            strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    } else {
                        strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                    }
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            } else {
                strPageBuffer.append("  [组织].[组织层次].&[" + map.get("customer_id_obj_ae") + "]  ");
            }
            strPageBuffer.append(" } ) ON COLUMNS FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");

                if (ss.length < 4) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[3]));
                    dxout = dxout.add(new BigDecimal(ss[4]));
                }
            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForDeviceMonth(Map<String, Object> map) {

        /*StringBuilder strBuilder = new StringBuilder();
        strBuilder.append(" ");
        strBuilder.append(" select sum(dxin) totalin,sum(dxout) totalout ");
        strBuilder.append(" from T_DEVICE_DATA_MONTH where 1=1 ");
        if ( BreezeeUtils.isNotEmpty(map.get("_customerId"))) {
            strBuilder.append(" and customer_id = '"+map.get("_customerId")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("customer_id_obj_ae"))) {
            strBuilder.append(" and shop_id = '"+map.get("customer_id_obj_ae")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("device_id_obj_ae"))) {
            strBuilder.append(" and device_id = '"+map.get("device_id_obj_ae")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("_year"))) {
            strBuilder.append(" and year = '"+map.get("_year")+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("happenTime_gt"))) {
            strBuilder.append(" and happen_time >= '" + DATE_FORMAT_SHORT.format(map.get("happenTime_gt"))+"' ");
        }
        if ( BreezeeUtils.isNotEmpty(map.get("happenTime_le"))) {
            strBuilder.append(" and happen_time <  '"+ DATE_FORMAT_SHORT.format(map.get("happenTime_le"))+"' ");
        }

        List<Map<String,Object>> resultList = jdbcTemplate.queryForList(strBuilder.toString());*/

        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append(" {  [设备].[设备ID].[设备ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS} on 1 ");
            strPageBuffer.append(" FROM (  SELECT ( { [时间].[年月日小时].[月].&[" + String.valueOf(map.get("_startDate")).substring(0, 7) + "]:[时间].[年月日小时].[月].&[" + String.valueOf(map.get("_endDate")).substring(0, 7) + "]}, {");

            if (map.get("_deviceIds") != null && CollectionUtils.isNotEmpty((List<String>) map.get("_deviceIds"))) {
                ((List<String>) map.get("_deviceIds")).stream().forEach(deviceId -> {
                    strPageBuffer.append("  [设备].[设备ID].[设备ID].&[" + deviceId + "] , ");
                });
                strPageBuffer.append("  [设备].[设备ID].[设备ID].&[-1] ");
            } else {
                strPageBuffer.append(" [设备].[设备ID].[设备ID].&[" + map.get("device_id_obj_ae") + "]  ");
            }

            strPageBuffer.append(" } ) ON COLUMNS  ");
            strPageBuffer.append(" FROM [客流]) ");


            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 4) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[3]));
                    dxout = dxout.add(new BigDecimal(ss[4]));
                }
            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    @Override
    public EmptyEntity sumForShopMonth(Map<String, Object> map) {

        List<Map<String, String>> resultList = Lists.newArrayList();
        try {

            Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
            OlapConnection con = (OlapConnection) DriverManager.getConnection("jdbc:xmla:Server=http://"+WebAppConstants.BI_LINK_IP+"/MDKL/msmdpump.dll;Catalog=" + WebAppConstants.BI_LINK_NAME);
            OlapWrapper wrapper = (OlapWrapper) con;
            OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
            OlapStatement stmt = olapConnection.createStatement();

            StringBuffer strPageBuffer = new StringBuffer();
            strPageBuffer.append(" ");
            strPageBuffer.append(" SELECT NON EMPTY { [Measures].[进], [Measures].[出] } ON 0, ");
            strPageBuffer.append("  { ");
            strPageBuffer.append(" [组织].[组织ID].[组织ID].ALLMEMBERS *  ");
            strPageBuffer.append(" [时间].[年].[年].ALLMEMBERS * ");
            strPageBuffer.append(" [时间].[月].[月].ALLMEMBERS } on 1 ");
            strPageBuffer.append(" FROM (  SELECT ( { [时间].[年月日小时].[月].&[" + String.valueOf(map.get("_startDate")).substring(0, 7) + "]:[时间].[年月日小时].[月].&[" + String.valueOf(map.get("_endDate")).substring(0, 7) + "]}, {");
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(map.get("_customerId") + "") &&
                    org.apache.commons.lang3.StringUtils.isEmpty(map.get("customer_id_obj_ae") + "")) {
                CustomerEntity parentCustomer = customerRepository.findOne(map.get("_customerId") + "");
                Set<CustomerEntity> shops = Sets.newConcurrentHashSet();
                parentCustomer.findDeepChildren(shops);
                Object shopType = map.get("shopType");
                shops.stream().forEach(shop -> {
                    if (shopType != null && !Objects.equals("", shopType)) {
                        if (Objects.equals(shop.getShopType(), shopType)) {
                            strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                        }
                    } else {
                        strPageBuffer.append("  [组织].[组织层次].&[" + shop.getId() + "] , ");
                    }
                });
                strPageBuffer.append("  [组织].[组织层次].&[-1] ");
            } else {
                strPageBuffer.append("  [组织].[组织层次].&[" + map.get("customer_id_obj_ae") + "]  ");
            }
            strPageBuffer.append("  } ) ON COLUMNS FROM [客流]) ");

            CellSet cellSet = stmt.executeOlapQuery(strPageBuffer.toString());

            Map<String, String> resultMap = Maps.newConcurrentMap();
            BigDecimal dxin = BigDecimal.ZERO;
            BigDecimal dxout = BigDecimal.ZERO;
            for (Position rowPos : cellSet.getAxes().get(1)) {
                String s = "";
                for (Member member : rowPos.getMembers()) {
                    s += member.getCaption() + "*";
                }
                for (Position colPos : cellSet.getAxes().get(0)) {
                    Cell cell = cellSet.getCell(colPos, rowPos);
                    s += cell.getFormattedValue() + "*";
                }
                String[] ss = s.split("\\*");
                if (ss.length < 4) {
                    dxin = dxin.add(BigDecimal.ZERO);
                    dxout = dxout.add(BigDecimal.ZERO);
                } else {
                    dxin = dxin.add(new BigDecimal(ss[3]));
                    dxout = dxout.add(new BigDecimal(ss[4]));
                }

            }
            resultMap.put("totalin", dxin.toString());
            resultMap.put("totalout", dxout.toString());
            resultList.add(resultMap);

        } catch (Exception e) {
            e.printStackTrace();
        }

        EmptyEntity resultEntity = new EmptyEntity();
        if (BreezeeUtils.isNotEmpty(resultList)) {
            resultEntity.getProperties().putAll(resultList.get(0));
        }

        return resultEntity;
    }

    private String sumTodayDxin(List<String> shopIdList) {

        String result = "0";
        if (shopIdList != null && shopIdList.size() > 0) {
            LocalDate date = LocalDate.now();
            String startDate = date.format(DateTimeFormatter.BASIC_ISO_DATE) + "000000";
            String endDate = date.format(DateTimeFormatter.BASIC_ISO_DATE) + "235959";

            String shopIdStr = "'" + Joiner.on("','").join(shopIdList) + "'";

            String sql = " select sum(dxin) from t_device_data " +
                    " where cus_id in (" + shopIdStr + ")" +
                    " and happen_time > '" + startDate + "'" +
                    " and happen_time < '" + endDate + "'";
            Integer cnt = jdbcTemplate.queryForObject(sql, Integer.class);
            result = (cnt == null) ? "0" : String.valueOf(cnt);
        }

        return result;
    }


}
